package com.nit.lab.mapper.system;

import com.nit.lab.dto.basic.TeacherDTO;
import com.nit.lab.dto.system.SchoolDTO;
import com.nit.lab.entity.system.SchoolEntity;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface SchoolMapper {

    /**
     * @Description: 获取表格数据，查询条件自行编写对应代码，需区分模糊和相等，排序条件无需改动
     * @Author: SN
     * @Date: 2019/01/30 18:15
     */
    @Select({"<script>SELECT " +
            "school_id AS `key`, school_id, school_code, school_name, " +
            "school_grade, school_info, gmt_create " +
            "FROM system_school " +
            "WHERE TRUE " +
            "<if test='schoolCode != null'> AND school_code LIKE CONCAT('%', #{schoolCode}, '%') </if>" +
            "<if test='schoolName != null'> AND school_name LIKE CONCAT('%', #{schoolName}, '%') </if>" +
            "ORDER BY " +
            "<if test='sortField != null'> ${sortField} ${sortOrder}, </if>" +
            "school_id desc " +
            "LIMIT #{start}, #{length}" +
            "</script>"})
    List<SchoolDTO> list4Table(
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("schoolCode") String schoolCode,
            @Param("schoolName") String schoolName,
            @Param("sortField") String sorterField,
            @Param("sortOrder") String sorterOrder) throws Exception;

    /**
     * @Description: 获取表格数据记录的总条数
     * @Author: SN
     * @Date: 2019/01/30 18:15
     */
    @Select({"<script>SELECT COUNT(*) " +
            "FROM system_school " +
            "WHERE TRUE " +
            "<if test='schoolCode != null'> AND school_code LIKE CONCAT('%', #{schoolCode}, '%') </if>" +
            "<if test='schoolName != null'> AND school_name LIKE CONCAT('%', #{schoolName}, '%') </if>" +
            "</script>"})
    Long count4Table(
            @Param("schoolCode") String schoolCode,
            @Param("schoolName") String schoolName) throws Exception;

    /**
     * @Description: 新增记录
     * @Author: SN
     * @Date: 2019/01/30 18:15
     */
    @Insert("INSERT INTO system_school (school_code, school_name, school_grade, school_info, gmt_create) " +
            "VALUES (#{schoolEntity.schoolCode}, #{schoolEntity.schoolName}, #{schoolEntity.schoolGrade}, " +
            "#{schoolEntity.schoolInfo}, #{schoolEntity.gmtCreate})")
    @Options(useGeneratedKeys = true, keyProperty = "schoolId", keyColumn = "school_id")
    Integer add(@Param("schoolEntity") SchoolEntity schoolEntity) throws Exception;

    /**
     * @Description: 更新记录
     * @Author: SN
     * @Date: 2019/01/30 18:15
     */
    @Update("UPDATE system_school " +
            "SET school_code = #{schoolEntity.schoolCode}, " +
            "school_name = #{schoolEntity.schoolName}, " +
            "school_grade = #{schoolEntity.schoolGrade}, " +
            "school_info = #{schoolEntity.schoolInfo} " +
            "WHERE school_id = #{schoolEntity.schoolId}")
    Integer update(@Param("schoolEntity") SchoolEntity schoolEntity) throws Exception;


    /**
     * @Description: 删除记录
     * @Author: SN
     * @Date: 2019/01/30 18:15
     */
    @Delete("DELETE FROM system_school WHERE school_id = #{schoolId}")
    Integer remove(@Param("schoolId") Long schoolId) throws Exception;

    /**
     * @Description: 查询学校list
     * @Author: zxq
     * @Date: 2020/8/20
     */
    @Select("SELECT school_id, school_name " +
            "FROM system_school " +
            "WHERE TRUE")
    @Results({
            @Result(column = "school_id", property = "schoolId"),
            @Result(column = "school_name", property = "schoolName")
    })
    List<SchoolDTO> listSchool() throws Exception;
}
